﻿Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports SAPbobsCOM

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="http://electra-ai.com/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class MasterData
    Inherits System.Web.Services.WebService
    <WebMethod()> _
    Public Function GetLogin(ByVal userId As String, ByVal passWord As String, ByVal dbType As String) As DataSet
        Try
            Dim b As New SAP_Functions
            Dim connect As New Connection()
            Dim ds As New DataSet("tbl_MasterUsers")
            Dim errMsg = connect.setDB("VSM")
            If errMsg.Length = 0 Then
                Dim query = "SELECT * FROM tbl_MasterUsers WHERE ID_User = '" & userId & "' AND Password = '" & passWord & "'"
                ds = connect.ObjectGetAll_Query(query, PublicVariable.VSM)
                If ((Not ds Is Nothing) And (ds.Tables.Item(0).Rows.Count > 0)) Then
                    Dim errVSMMsg = connect.connectDB(PublicVariable.VSM)
                    If (errVSMMsg.Length > 0) Then
                        Return b.ReturnMessage(-9000, errVSMMsg)
                    End If
                    Dim errDEALERMsg = connect.connectDB(PublicVariable.DEALER)
                    If (errDEALERMsg.Length > 0) Then
                        Return b.ReturnMessage(-9001, errDEALERMsg)
                    End If
                End If
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetWareHouseByUser(ByVal dealerID As String, ByVal department As String, ByVal dbType As String) As DataSet
        Try
            Dim b As New SAP_Functions
            Dim connect As New Connection()
            Dim ds As New DataSet("OWHS")
            Dim errMsg = connect.setDB(dbType)
            If errMsg.Length = 0 Then
                Dim query = "SELECT * FROM OWHS WHERE U_DealerID = '" & dealerID & "' AND U_Department = '" & department & "'"
                ds = connect.ObjectGetAll_Query(query, dbType)
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetBPGroup(ByVal CardType As String, ByVal dbType As String) As DataSet
        Try
            Dim ds As New DataSet("OCRG")
            Dim connect As New Connection()

            Dim str As String = ""
            If CardType = "S" Then
                str = "select GroupCode,GroupName from OCRG where GroupType='S'"
            Else
                str = "select GroupCode,GroupName from OCRG where GroupType<>'S'"
            End If

            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function GetDealers(ByVal dbType As String) As DataSet
        Try
            Dim ds As New DataSet("OCRD")
            Dim connect As New Connection()

            Dim str As String = "select CardCode, CardName from OCRD where CardType='C' and GroupCode = '104' "

            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function GetDealerByID(ByVal dbType As String, ByVal ID_Dealer As String) As DataSet
        Try
            Dim ds As New DataSet("OCRD")
            Dim connect As New Connection()

            Dim str As String = "select CardCode, CardName, U_ContactPerson from OCRD where CardType='C' and GroupCode = '104' and CardCode = '" + ID_Dealer + "'"

            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function GetCustomers(ByVal dbType As String, ByVal ID_Dealer As String, ByVal GroupCode As String) As DataSet
        Try
            Dim ds As New DataSet("OCRD")
            Dim connect As New Connection()
            Dim str As String = String.Empty, conditionstr As String = String.Empty

            If dbType = PublicVariable.VSM Then
                str = "select CardCode, CardName, Address, E_Mail, CardType, U_ContactPerson, U_Remain,Currency from OCRD where CardType='C' and GroupCode in " & GroupCode
            ElseIf dbType = PublicVariable.DEALER Then
                str = "select CardCode, CardName, Address, E_Mail, CardType, U_ContactPerson, U_Remain,Currency " _
                    & "from OCRD where CardType='C' and ( GroupCode in (103) or  GroupCode in " + GroupCode + " )"
                'str = "select OCRD.CardCode, OCRD.CardName, OCRD.Address, OCRD.E_Mail, OCRD.CardType, OCRD.SlpCode " _
                '    & "from OCRD inner join tbl_CustomerDealers CD on OCRD.CardCode = CD.CardCode where OCRD.CardType='C' and CD.ID_Dealer = '" & ID_Dealer & "'"
            End If

            If ID_Dealer = "VSM" Or ID_Dealer = "" Then ' Dealer is VSM Get all customer

            Else ' get only customers' dealer
                str = str + " and ( U_DealerCode = '" + ID_Dealer + "' or CardCode = '" + ID_Dealer + "' )"
            End If

            ' Only get Active Customer --> thangnv added 2014/07/21
            str = str + " and CardCode not in (select CardCode from OCRD where ValidFor = 'N' and frozenFor = 'Y') "

            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetCustomerBalance(ByVal dbType As String, ByVal ID_Dealer As String, ByVal GroupCode As String) As DataSet
        Try
            Dim ds As New DataSet("OCRD")
            Dim connect As New Connection()
            Dim str As String = String.Empty, conditionstr As String = String.Empty

            If dbType = PublicVariable.VSM Then
                str = "select Balance,CardCode, CardName, Address, E_Mail, CardType, U_ContactPerson, U_Remain,Currency from OCRD where CardType='C' and GroupCode in " & GroupCode
            ElseIf dbType = PublicVariable.DEALER Then
                str = "select Balance,CardCode, CardName, Address, E_Mail, CardType, U_ContactPerson, U_Remain,Currency " _
                    & "from OCRD where CardType='C' and ( GroupCode in (103) or  GroupCode in " + GroupCode + " )"
                'str = "select OCRD.CardCode, OCRD.CardName, OCRD.Address, OCRD.E_Mail, OCRD.CardType, OCRD.SlpCode " _
                '    & "from OCRD inner join tbl_CustomerDealers CD on OCRD.CardCode = CD.CardCode where OCRD.CardType='C' and CD.ID_Dealer = '" & ID_Dealer & "'"
            End If

            If ID_Dealer = "VSM" Or ID_Dealer = "" Then ' Dealer is VSM Get all customer

            Else ' get only customers' dealer
                str = str + " and ( U_DealerCode = '" + ID_Dealer + "' or CardCode = '" + ID_Dealer + "' )"
            End If

            ' Only get Active Customer --> thangnv added 2014/07/21
            str = str + " and CardCode not in (select CardCode from OCRD where ValidFor = 'N' and frozenFor = 'Y') "

            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function GetVendors(ByVal dbType As String, ByVal ID_Dealer As String) As DataSet
        Try
            Dim ds As New DataSet("OCRD")
            Dim connect As New Connection()
            Dim str As String = String.Empty

            If dbType = PublicVariable.VSM Then
                str = "select CardCode, CardName, Address, E_Mail, CardType, SlpCode, CntctPrsn, U_ContactPerson, U_Remain,Currency from OCRD where CardType='S' "
            ElseIf dbType = PublicVariable.DEALER Then
                'str = "select OCRD.CardCode, OCRD.CardName, OCRD.Address, OCRD.E_Mail, OCRD.CardType, OCRD.SlpCode, OCRD.U_ContactPerson " _
                '    & "from OCRD inner join tbl_CustomerDealers CD on OCRD.CardCode = CD.CardCode where OCRD.CardType='S' and CD.ID_Dealer = '" & ID_Dealer & "'"
                str = "select CardCode, CardName, Address, E_Mail, CardType, SlpCode, CntctPrsn, U_ContactPerson, U_Remain,Currency from OCRD where CardType='S' "
            End If

            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function GetCustomerByCode(ByVal dbType As String, ByVal CardCode As String) As DataSet
        Try
            Dim ds As New DataSet("OCRD")
            Dim connect As New Connection()
            Dim str As String = "select * from OCRD where CardType='C' and CardCode = '" & CardCode & "'"

            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function GetSalesMan(ByVal dbType As String, ByVal ID_Dealer As String) As DataSet
        Try
            Dim str As String = String.Empty
            Dim ds As New DataSet("OSLP")
            Dim connect As New Connection()
            If dbType = PublicVariable.VSM Then
                str = "select SlpCode, SlpName from OSLP order by SlpCode "
            Else
                str = "select SlpCode, SlpName from OSLP where (U_Code = '" + ID_Dealer + "' or SlpCode = -1) order by SlpCode "
            End If
            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function GetSalesManByCode(ByVal dbType As String, ByVal ID_Dealer As String, ByVal SlpCode As String) As DataSet
        Try
            Dim ds As New DataSet("OSLP")
            Dim connect As New Connection()
            Dim str As String = "select * from OSLP where SlpCode = '" & SlpCode & "'"
            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function GetManagerBySaleManCode(ByVal dbType As String, ByVal ID_Dealer As String, ByVal SlpCode As String) As DataSet
        Try
            Dim ds As New DataSet("OSLP")
            Dim connect As New Connection()
            Dim str As String = "select * from OSLP where SlpCode = (select U_Manager from OSLP where SlpCode = '" + SlpCode + "') "
            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function GetContactPerson(ByVal dbType As String, ByVal ID_Dealer As String, ByVal CardCode As String) As DataSet
        Try
            Dim ds As New DataSet("OCPR")
            Dim connect As New Connection()
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("select CntctCode Code, Name FirstName,'' LastName, 0 IsDefault from OCPR where CardCode='" + CardCode + "'", dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function GetItemsByItemGroup(ByVal dbType As String, ByVal ID_Dealer As String, ByVal ItmGrp As String) As DataSet
        Try
            Dim ds As New DataSet("OITM")
            Dim connect As New Connection()
            Dim str As String = "Select ItemCode, ItemName, OnHand, U_ProductSales From OITM Where ItmsGrpCod = " & ItmGrp
            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetItemsByItemGroupTop(ByVal dbType As String, ByVal ID_Dealer As String, ByVal ItmGrp As String) As DataSet
        Try
            Dim ds As New DataSet("OITM")
            Dim connect As New Connection()
            Dim str As String = "Select Top 5000 * From OITM Where ItmsGrpCod = " & ItmGrp
            str += " Order By OnHand DESC "
            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetPartsItemsByItemGroup(ByVal dbType As String, ByVal ID_Dealer As String, ByVal ItmGrp As String) As DataSet
        Try
            Dim ds As New DataSet("OITM")
            Dim connect As New Connection()
            Dim str As String = "SELECT  * FROM ITM1 T0 "
            str += " JOIN OITM T1 ON T0.ItemCode = T1.ItemCode"
            str += " JOIN OPLN T2 ON T0.PriceList = T2.ListNum WHERE T2.ListNum =2  AND ItmsGrpCod = " & ItmGrp
            str += " Order By OnHand DESC "
            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetItemsByItemCodeList(ByVal dbType As String, ByVal ID_Dealer As String, ByVal ItemCodeList As String) As DataSet
        Try
            Dim ds As New DataSet("OITM")
            Dim connect As New Connection()
            Dim str As String = "Select * From OITM Where ItemCode in (" & ItemCodeList & ")"
            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function GetItemsByModelProductColor(ByVal dbType As String, ByVal ID_Dealer As String, ByVal Model As String, ByVal Product As String, ByVal Color As String) As DataSet
        Try
            Dim ds As New DataSet("OITM")
            Dim connect As New Connection()
            Dim str As String = "Select * From OITM Where U_ModelSales = '" + Model + "' and U_ProductSales = '" + Product + "' and U_Color = '" + Color + "'"
            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetItemsByModelProduct(ByVal dbType As String, ByVal ID_Dealer As String, ByVal Model As String, ByVal Product As String) As DataSet
        Try
            Dim ds As New DataSet("OITM")
            Dim connect As New Connection()
            Dim str As String = "SELECT TOP 1* FROM OITM WHERE U_ModelSales = '" + Model + "' and U_ProductSales = '" + Product + "'"
            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function GetSerialNumbers(ByVal dbType As String, ByVal ID_Dealer As String, ByVal ItemCode As String, ByVal WhsCode As String) As DataSet
        Try
            Dim ds As New DataSet("OSRI")
            Dim connect As New Connection()
            Dim str As String = "Select * From OSRI Where Status = 0 and ItemCode = '" & ItemCode & "' and WhsCode = '" & WhsCode & "'"
            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function GetDisplaySetting(ByVal dbType As String, ByVal ID_Dealer As String) As DataSet
        Try
            Dim ds As New DataSet("OADM")
            Dim connect As New Connection()
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("select CompnyName,DecSep,ThousSep,SumDec,PriceDec,QtyDec,PercentDec,RateDec,DateFormat,DateSep from OADM", dbType)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function GetAccountMasterData(ByVal dbType As String, ByVal ID_Dealer As String, ByVal AccountCode As String, ByVal Filter As String) As DataSet
        Try
            Dim ds As New DataSet("OACT")
            Dim connect As New Connection()
            Dim str As String
            str = "select AcctCode,AcctName,FrgnName from OACT where Postable='Y'"
            Select Case Filter
                Case "Revenue"
                    str = str + " and ActType='I'"
                Case "AR"
                    str = str + " and LocManTran='Y' and GroupMask=1"
                Case "AP"
                    str = str + " and LocManTran='Y' and GroupMask=2"
            End Select

            If String.IsNullOrEmpty(AccountCode) = False Then
                str = str + " and AcctCode Like '" + AccountCode + "%'"
            End If

            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function GetWarehouse(ByVal dbType As String, ByVal ID_Dealer As String) As DataSet
        Try
            Dim ds As New DataSet("OWHS")
            Dim connect As New Connection()
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("Select WhsCode,WhsName from OWHS order by WhsCode", dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function GetModelList(ByVal dbType As String) As DataSet
        Try
            Dim ds As New DataSet("MODELSALES")
            Dim connect As New Connection()
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("Select Code, Name from [@MODELSALES] order by Code", dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function GetProductByModel(ByVal dbType As String, ByVal ModelCode As String) As DataSet
        Try
            Dim ds As New DataSet("PRODUCTSALES")
            Dim connect As New Connection()
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("Select Code, Name, U_Model from [@PRODUCTSALES] where U_Model = '" + ModelCode + "' order by Code", dbType)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function GetColorList(ByVal dbType As String) As DataSet
        Try
            Dim ds As New DataSet("COLORCODE")
            Dim connect As New Connection()
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("Select Code, Name from [@COLORCODE] order by Code", dbType)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function GetFreightList(ByVal dbType As String) As DataSet
        Try
            Dim ds As New DataSet("OEXD")
            Dim connect As New Connection()
            connect.setDB(dbType)
            Dim str1 As String = "select ROW_NUMBER() Over(Order By O.ExpnsCode) No, O.ExpnsCode, O.ExpnsName, 'O' Status, '' Comments, " _
                + " 0 LineTotal, O.ExpnsCode As LineNum, '' DocEntry from OEXD O order by O.ExpnsCode "
            ds = connect.ObjectGetAll_Query(str1, dbType)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    <WebMethod()> _
    Public Function GetPartsFreightList(ByVal dbType As String, ByVal dealerId As String, ByVal department As String, ByVal sqDocEntry As Integer) As DataSet
        Try
            Dim ds As New DataSet("OEXD")
            Dim connect As New Connection()
            connect.setDB(dbType)
            Dim str1 As String = "SELECT ROW_NUMBER() Over(Order By ID) No,* FROM U_Part_fee WHERE DealerID ='" & dealerId & "' "
            str1 += " AND Department ='" & department & "' AND SQDocEntry= " & sqDocEntry
            ds = connect.ObjectGetAll_Query(str1, dbType)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function GetItemGroupByCode(ByVal dbType As String, ByVal ID_Dealer As String, ByVal ItemCode As String, ByVal KeyName As String) As String
        Dim f As New SAP_Functions
        Dim connect As New Connection()
        connect.setDB(dbType)
        Return f.GetSingleValueBySqlCommand(dbType, "select ItmsGrpCod from OITM where ItemCode = '" + ItemCode + "'", KeyName)
    End Function

    <WebMethod()> _
    Public Function GetItemsAdvancePayment(ByVal dbType As String, ByVal DocEntry As String) As DataSet
        Try
            Dim connect As New Connection()
            connect.setDB(dbType)
            Dim str As String = "SELECT A.ItemCode, SUM(A.Quantity) Quantity, BaseLine "
            str += "FROM ( "
            str += "    select ItemCode, SUM(Quantity) As Quantity, U_BaseLine BaseLine from dbo.tbl_RDR1 where NumAtCard = " + DocEntry + " and Remain = 0 and (ItemCode is not null and ItemCode <> '') group by ItemCode, U_BaseLine "
            str += "union all "
            str += "    select ItemCode, SUM(Quantity) * -1 As Quantity, BaseLine from dbo.DLN1 where DocEntry in (select DocEntry from ODLN where NumAtCard = " + DocEntry + " ) group by ItemCode, BaseLine "
            str += ") A GROUP BY A.ItemCode, BaseLine HAVING SUM(A.Quantity) > 0 "

            Return connect.ObjectGetAll_Query(str, dbType)
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function GetMaketingDocConfirmList(ByVal dbType As String, ByVal ID_Dealer As String, ByVal DocType As String, ByVal department As String, _
                                             ByVal fromDate As Date, ByVal toDate As Date, ByVal soNumber As String) As DataSet
        Dim ds As DataSet
        Dim connect As New Connection()
        connect.setDB(dbType)

        Dim str As String
        Dim condition As String = String.Empty

        str = "select DocEntry, DocDate, DocDueDate, CardCode, CardName, Comments, DocTotal,U_SONBR  "
        Select Case DocType
            Case "18"
                str = str + " from OPCH"
            Case "19"
                str = str + " from ORPC"
            Case "20"
                str = str + " from OPDN"
            Case "21"
                str = str + " from ORPD"
            Case "22"
                str = str + ", 'New' As U_Updatestatus from OPOR"
                condition = " where DocStatus = 'O' and U_Updatestatus = '0' AND U_Department = '" & department & "' AND U_DealerID ='" & ID_Dealer & "'"

            Case "13"
                str = str + " from OINV"
            Case "14"
                str = str + " from ORIN"
            Case "15"
                str = str + " from ODLN"

            Case "17"
                str = str + ", U_OrderConfirm from ORDR "
                If ID_Dealer = PublicVariable.VSM Then
                    condition = " where U_OrderConfirm in ('2') and U_SOType in ('0', '1') AND U_Department = '" & department & "'"
                Else
                    condition = " where U_OrderConfirm in ('2') and U_SOType in ('0', '1') AND U_Department = '" & department & "' AND CardCode='" & ID_Dealer & "'"
                End If

            Case "23"
                str = str + " from OQUT"

            Case "24"
                str = "select DocNum, DocDate, DocDueDate, CardCode, CardName, Comments, DocTotal, '' DocStatus from ORCT"

            Case "46"
                str = "select DocNum, DocDate, DocDueDate, CardCode, CardName, Comments, DocTotal, '' DocStatus from OVPM"

            Case "59"
                str = str + " from OIGN"
            Case "60"
                str = str + " from OIGE"

            Case "67"
                str = str + " from OWTR"

            Case "1250000001"
                str = str + " from OWTQ"

            Case "97"
                str = str + " from OOPR"
        End Select

        ' Build condition to query
        str = str + condition
        str = str + " AND DocDate BETWEEN @Param1 AND @Param2"
        If soNumber.Length > 0 Then
            str = str + " AND U_SONBR LIKE '%" & soNumber & "%'"
        End If

        ds = connect.ObjectGetAll_Query(str, New Object() {fromDate, toDate}, dbType)
        Return ds

    End Function
    <WebMethod()> _
    Public Function GetMaketingDocPendingList(ByVal dbType As String, ByVal ID_Dealer As String, ByVal DocType As String, ByVal department As String) As DataSet
        Dim ds As DataSet
        Dim connect As New Connection()
        connect.setDB(dbType)

        Dim str As String
        Dim condition As String = String.Empty

        str = "select DocEntry, DocDate, DocDueDate, CardCode, CardName, Comments, DocTotal,U_SONBR  "
        Select Case DocType
            Case "18"
                str = str + " from OPCH"
            Case "19"
                str = str + " from ORPC"
            Case "20"
                str = str + " from OPDN"
            Case "21"
                str = str + " from ORPD"
            Case "22"
                str = str + ", 'New' As U_Updatestatus from OPOR"
                condition = " where DocStatus = 'O' and U_Updatestatus = '0' AND U_Department = '" & department & "' AND U_DealerID ='" & ID_Dealer & "'"

            Case "13"
                str = str + " from OINV"
            Case "14"
                str = str + " from ORIN"
            Case "15"
                str = str + " from ODLN"

            Case "17"
                str = str + ", U_OrderConfirm from ORDR "
                If ID_Dealer = PublicVariable.VSM Then
                    condition = " where DocStatus = 'O' and U_OrderConfirm in ('0', '1', '4') and U_SOType in ('0', '1','2') AND U_Department = '" & department & "'"
                Else
                    condition = " where DocStatus = 'O' and U_OrderConfirm in ('1') and U_SOType in ('0', '1','2') AND U_Department = '" & department & "' AND CardCode='" & ID_Dealer & "'"
                End If

            Case "23"
                str = str + " from OQUT"

            Case "24"
                str = "select DocNum, DocDate, DocDueDate, CardCode, CardName, Comments, DocTotal, '' DocStatus from ORCT"

            Case "46"
                str = "select DocNum, DocDate, DocDueDate, CardCode, CardName, Comments, DocTotal, '' DocStatus from OVPM"

            Case "59"
                str = str + " from OIGN"
            Case "60"
                str = str + " from OIGE"

            Case "67"
                str = str + " from OWTR"

            Case "1250000001"
                str = str + " from OWTQ"

            Case "97"
                str = str + " from OOPR"
        End Select

        ' Build condition to query
        str = str + condition

        ds = connect.ObjectGetAll_Query(str, dbType)
        Return ds

    End Function
    <WebMethod()> _
    Public Function GetMaketingDocPartsPendingList(ByVal dbType As String, ByVal ID_Dealer As String, ByVal DocType As String, ByVal department As String) As DataSet
        Dim ds As DataSet
        Dim connect As New Connection()
        connect.setDB(dbType)

        Dim str As String
        Dim condition As String = String.Empty

        str = "select DocEntry, DocDate, DocDueDate, CardCode, CardName, Comments, DocTotal  "
        Select Case DocType
            Case "18"
                str = str + " from OPCH"
            Case "19"
                str = str + " from ORPC"
            Case "20"
                str = str + " from OPDN"
            Case "21"
                str = str + " from ORPD"
            Case "22"
                str = str + ", 'New' As U_Updatestatus from OPOR"
                condition = " where  U_SOType = '3' AND  DocStatus = 'O' and U_Updatestatus = '0' AND U_Department = '" & department & "' "

            Case "13"
                str = str + " from OINV"
            Case "14"
                str = str + " from ORIN"
            Case "15"
                str = str + " from ODLN"

            Case "17"
                str = str + ", U_OrderConfirm from ORDR "
                If ID_Dealer = PublicVariable.VSM Then
                    condition = " where U_SOType = '3' AND DocStatus = 'O' and U_OrderConfirm in ('0', '1', '4')  AND U_Department = '" & department & "'"
                Else
                    condition = " where  U_SOType = '3' AND DocStatus = 'O' and U_OrderConfirm in ('1')  AND U_Department = '" & department & "' AND CardCode='" & ID_Dealer & "'"
                End If

            Case "23"
                str = str + " from OQUT"

            Case "24"
                str = "select DocNum, DocDate, DocDueDate, CardCode, CardName, Comments, DocTotal, '' DocStatus from ORCT"

            Case "46"
                str = "select DocNum, DocDate, DocDueDate, CardCode, CardName, Comments, DocTotal, '' DocStatus from OVPM"

            Case "59"
                str = str + " from OIGN"
            Case "60"
                str = str + " from OIGE"

            Case "67"
                str = str + " from OWTR"

            Case "1250000001"
                str = str + " from OWTQ"

            Case "97"
                str = str + " from OOPR"
        End Select

        ' Build condition to query
        str = str + condition

        ds = connect.ObjectGetAll_Query(str, dbType)
        Return ds

    End Function
    <WebMethod()> _
    Public Function GetSingleValue(ByVal dbType As String, ByVal SqlCommand As String, ByVal KeyName As String) As String
        Dim f As New SAP_Functions
        Dim connect As New Connection()
        connect.setDB(dbType)
        Return f.GetSingleValueBySqlCommand(dbType, SqlCommand, KeyName)
    End Function

    <WebMethod()> _
    Public Function GetMasterListBySqlCommand(ByVal dbType As String, ByVal SqlCommand As String) As DataSet
        Dim f As New SAP_Functions
        Dim connect As New Connection()
        connect.setDB(dbType)
        Return f.GetMasterListBySqlCommand(dbType, SqlCommand)
    End Function

    <WebMethod()> _
    Public Function GetTblRDR1(ByVal dbType As String, ByVal ID_Dealer As String, ByVal DocEntry As String, ByVal TableLineName As String) As DataSet
        Try
            Dim str As String = String.Empty
            Dim connect As New Connection()
            connect.setDB(dbType)
            If TableLineName.CompareTo("tbl_RDR1") = 0 Then
                str = "Select ROW_NUMBER() Over(Order By DocEntry) No, *, "
                str += " ( 1.00 * ( isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0) ) + ( (isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0)) * isNull(VatPrcnt, 0) /100) ) As SumApplied, "
                str += " Remain As Remain_Org, CardCode from " + TableLineName + " where DocEntry = " + DocEntry + " and Remain >= 0 "
            Else
                str = "Select ROW_NUMBER() Over(Order By DocEntry) No, 0 Selected, *, LineNum as U_BaseLine, "
                str += " (1.00 * ( isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0) ) + ( (isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0)) * isNull(VatPrcnt, 0) /100)) As SumApplied, 0.00 Paid, "
                str += " ( (isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0) ) + ( (isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0)) * isNull(VatPrcnt, 0) /100)) As Remain, "
                str += " ( (isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0)) + ( (isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0)) * isNull(VatPrcnt, 0) /100)) As Remain_Org, '' CardCode "
                str += " from " + TableLineName + " where DocEntry = " + DocEntry
            End If
            Return connect.ObjectGetAll_Query(str, dbType)
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function GetTblRDR1RemainAmnt(ByVal dbType As String, ByVal ID_Dealer As String, ByVal NumAtCard As String) As DataSet
        Try
            Dim str As String = String.Empty
            Dim connect As New Connection()
            connect.setDB(dbType)
            str = "Select ROW_NUMBER() Over(Order By DocEntry) No, U_Discount, U_IncentiveDiscount, VatGroup, VatPrcnt, VatSum, U_BaseLine, "
            str += " 0 Selected, DocEntry, LineNum, NumAtCard, U_Color, U_Model, U_Product, ItemCode, Dscription, Quantity, PriceBefDi, DiscPrcnt, Price, LineTotal, ShipDate, 0 Paid, Remain, internal_id, "
            str += " (1.00 * (isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0)) + ( (isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0)) * isNull(VatPrcnt, 0) /100)) As SumApplied, "
            str += " Remain As Remain_Org, CardCode from tbl_RDR1 "
            str += " where NumAtCard = " + NumAtCard + " and Remain > 0 and DocEntry = ( select Max(DocEntry) from tbl_RDR1 " + " where NumAtCard = " + NumAtCard + " ) "
            Return connect.ObjectGetAll_Query(str, dbType)
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function GetCustRemainAmt(ByVal dbType As String, ByVal ID_Dealer As String, ByVal CardCode As String) As DataSet
        Try
            Dim ds As New DataSet("tbl_RDR1")
            Dim connect As New Connection()
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("Select '0' No, " _
                                            + " 0 Selected, U_Color, U_Model, U_Product, ItemCode, Dscription, 0 Quantity, 0 PriceBefDi, 0 DiscPrcnt, 0 Price, 0 LineTotal, " _
                                            + " ShipDate, 0 DocEntry, 0 LineNum, (1.00 * isNull(LineTotal, 0) + (isNull(LineTotal, 0) * 10 /100)) As SumApplied, 0 Paid, Remain, " _
                                            + " Remain As Remain_Org, NumAtCard, CardCode from tbl_RDR1 " _
                                            + " where Paid = 0 and Remain < 0 and CardCode = '" + CardCode + "' and internal_id = ( select Max(internal_id) from tbl_RDR1 where CardCode = '" + CardCode + "' )", dbType)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function GetCustomerDetails(ByVal dbType As String, ByVal CardCode As String, ByVal Type As String) As DataSet
        Try
            Dim ds As New DataSet("tbl_OCRD10")
            Dim connect As New Connection()
            connect.setDB(dbType)
            If Type = "D" Then
                ds = connect.ObjectGetAll_Query("select * from tbl_DCRD10 where CardCode = '" + CardCode + "'", dbType)
            Else
                ds = connect.ObjectGetAll_Query("select ROW_NUMBER() Over(Order By O.internal_id) No, A.internal_id, O.internal_id As Code, O.Name, A.CardCode, isNull(A.Selected, 0) As Selected " _
                                                + " from tbl_" & Type & "CRD10 A right outer join tbl_OCRD10 O on A.Code = O.internal_id and A.CardCode = '" + CardCode + "' where MasterType = '" + Type + "'", dbType)
            End If

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function GetWarehouseByDealerDepartment(ByVal dbType As String, ByVal ID_Dealer As String, ByVal Department As String) As DataSet
        Try
            Dim ds As New DataSet("OWHS")
            Dim strCondition As String = String.Empty
            strCondition = " where U_Department = '" & Department & "'"
            If String.IsNullOrEmpty(ID_Dealer) = False And ID_Dealer.CompareTo(PublicVariable.VSM) <> 0 Then
                strCondition = strCondition + " and U_DealerID = '" & ID_Dealer & "'"
            End If
            Dim connect As New Connection()
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("Select WhsCode, WhsName from OWHS " & strCondition & " order by WhsCode", dbType)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    <WebMethod()> _
    Public Function GetPartsGrossPrice(ByVal dbType As String, ByVal ID_Dealer As String, ByVal itemCode As String, ByVal priceType As String) As Double
        Dim connect As New Connection()
        connect.setDB(dbType)
        Dim dt As DataTable
        Dim str As String = ""
        str += " SELECT ISNULL(t0.Price,0) GrossPrice FROM ITM1 T0 "
        str += " JOIN OITM T1 ON T0.ItemCode = T1.ItemCode"
        str += " JOIN OPLN T2 ON T0.PriceList = T2.ListNum"
        str += " WHERE T0.ItemCode='" + itemCode + "' AND T2.ListNum = " + priceType + ""
        dt = connect.ObjectGetAll_Query(str, dbType).Tables(0)
        If dt.Rows.Count = 0 Then
            Return 0
        Else
            Return dt.Rows(0).Item("GrossPrice")
        End If
    End Function
    <WebMethod()> _
    Public Function GetAllCustomers(ByVal dbType As String, ByVal itemCode As String) As DataSet
        Try
            Dim dsReturn As New DataSet("OCRD")
            Dim connect As New Connection()
            connect.setDB(dbType)
            Dim dsCus As DataSet = connect.ObjectGetAll_Query("SELECT * FROM OCRD WHERE CardType = 'C' AND GroupCode = 104", dbType)
            dsCus.Tables(0).TableName = "T1"
            connect.setDB("DEALER")
            Dim dsWhs As DataSet = connect.ObjectGetAll_Query("SELECT * FROM OWHS WHERE  U_Department='PAR'", "DEALER")
            dsWhs.Tables(0).TableName = "T2"
            connect.setDB("DEALER")
            Dim dsItem As DataSet = connect.ObjectGetAll_Query("SELECT SUM(OnHand) AS OnHand,WhsCode FROM OITW WHERE  ItemCode='" & itemCode & "' GROUP BY WhsCode", "DEALER")
            dsItem.Tables(0).TableName = "T3"

            dsReturn.Tables.Add(dsCus.Tables(0).Copy())
            dsReturn.Tables.Add(dsWhs.Tables(0).Copy())
            dsReturn.Tables.Add(dsItem.Tables(0).Copy())
            Return dsReturn
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    <WebMethod()> _
    Public Function GetVSMItemProcessing(ByVal dbType As String, ByVal itemCode As String) As DataSet
        Try
            Dim ds As DataSet
            Dim tbFormat As New DataTable
            tbFormat.Columns.Add("Purchased")
            tbFormat.Columns.Add("Received")
            tbFormat.Columns.Add("TBO")
            tbFormat.Columns.Add("ABO")
            tbFormat.Columns.Add("OnStock")
            tbFormat.Columns.Add("Ordered")
            tbFormat.Columns.Add("Shipped")
            tbFormat.Columns.Add("Total")
            Dim dsReturn As New DataSet()

            Dim row As DataRow = tbFormat.NewRow()

            Dim connect As New Connection()
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("SELECT ISNULL(SUM(Quantity),0) AS Purchased FROM POR1 WHERE  ItemCode='" & itemCode & "'", dbType)
            If ds IsNot Nothing AndAlso ds.Tables.Count AndAlso ds.Tables(0).Rows.Count > 0 Then
                row("Purchased") = ds.Tables(0).Rows(0)("Purchased").ToString()
            End If
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("SELECT ISNULL(SUM(Quantity),0) AS Received FROM PDN1 WHERE  ItemCode='" & itemCode & "'", dbType)
            If ds IsNot Nothing AndAlso ds.Tables.Count AndAlso ds.Tables(0).Rows.Count > 0 Then
                row("Received") = ds.Tables(0).Rows(0)("Received")
            End If
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("SELECT ISNULL(SUM(OpenQty),0) AS OpenQty FROM POR1 WHERE  ItemCode='" & itemCode & "'", dbType)
            If ds IsNot Nothing AndAlso ds.Tables.Count AndAlso ds.Tables(0).Rows.Count > 0 Then
                row("TBO") = ds.Tables(0).Rows(0)("OpenQty")
            End If
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("SELECT ISNULL(SUM(OpenQty),0) AS OpenQty FROM POR1 WHERE  ItemCode='" & itemCode & "'", dbType)
            If ds IsNot Nothing AndAlso ds.Tables.Count AndAlso ds.Tables(0).Rows.Count > 0 Then
                row("ABO") = 0 'ds.Tables(0).Rows(0)("OpenQty")
            End If
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("SELECT ISNULL(SUM(OnHand),0) AS OnStock FROM OITW  WHERE  ItemCode='" & itemCode & "'", dbType)
            If ds IsNot Nothing AndAlso ds.Tables.Count AndAlso ds.Tables(0).Rows.Count > 0 Then
                row("OnStock") = ds.Tables(0).Rows(0)("OnStock")
            End If
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("SELECT ISNULL(SUM(Quantity),0) AS Ordered FROM RDR1 WHERE  ItemCode='" & itemCode & "'", dbType)
            If ds IsNot Nothing AndAlso ds.Tables.Count AndAlso ds.Tables(0).Rows.Count > 0 Then
                row("Ordered") = ds.Tables(0).Rows(0)("Ordered")
            End If
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("SELECT ISNULL(SUM(Quantity),0) AS Shipped FROM DLN1 WHERE  ItemCode='" & itemCode & "'", dbType)
            If ds IsNot Nothing AndAlso ds.Tables.Count AndAlso ds.Tables(0).Rows.Count > 0 Then
                row("Shipped") = ds.Tables(0).Rows(0)("Shipped")
            End If
            row("Total") = Double.Parse(row("Ordered")) + Double.Parse(row("OnStock")) - Double.Parse(row("Received"))
            tbFormat.Rows.Add(row)
            dsReturn.Tables.Add(tbFormat)
            Return dsReturn
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    <WebMethod()> _
    Public Function GetDealerItemProcessing(ByVal dbType As String, ByVal itemCode As String) As DataSet
        Try
            Dim ds As DataSet
            Dim tbFormat As New DataTable
            tbFormat.Columns.Add("Purchased")
            tbFormat.Columns.Add("Received")
            tbFormat.Columns.Add("TBO")
            tbFormat.Columns.Add("ABO")
            tbFormat.Columns.Add("OnStock")
            tbFormat.Columns.Add("Ordered")
            tbFormat.Columns.Add("Shipped")
            tbFormat.Columns.Add("Total")
            Dim dsReturn As New DataSet()

            Dim row As DataRow = tbFormat.NewRow()

            Dim connect As New Connection()
            connect.setDB("VSM")
            ds = connect.ObjectGetAll_Query("SELECT ISNULL(SUM(Quantity),0) AS Purchased FROM RDR1 WHERE  ItemCode='" & itemCode & "'", "VSM")
            If ds IsNot Nothing AndAlso ds.Tables.Count AndAlso ds.Tables(0).Rows.Count > 0 Then
                row("Purchased") = ds.Tables(0).Rows(0)("Purchased").ToString()
            End If
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("SELECT ISNULL(SUM(Quantity),0) AS Received FROM PDN1 WHERE  ItemCode='" & itemCode & "'", dbType)
            If ds IsNot Nothing AndAlso ds.Tables.Count AndAlso ds.Tables(0).Rows.Count > 0 Then
                row("Received") = ds.Tables(0).Rows(0)("Received")
            End If
            connect.setDB("VSM")
            ds = connect.ObjectGetAll_Query("SELECT ISNULL(SUM(OpenQty),0) AS OpenQty FROM RDR1 WHERE  ItemCode='" & itemCode & "'", "VSM")
            If ds IsNot Nothing AndAlso ds.Tables.Count AndAlso ds.Tables(0).Rows.Count > 0 Then
                row("TBO") = ds.Tables(0).Rows(0)("OpenQty")
            End If
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("SELECT ISNULL(SUM(OpenQty),0) AS OpenQty FROM RDR1 WHERE  ItemCode='" & itemCode & "'", dbType)
            If ds IsNot Nothing AndAlso ds.Tables.Count AndAlso ds.Tables(0).Rows.Count > 0 Then
                row("ABO") = 0 'ds.Tables(0).Rows(0)("OpenQty")
            End If
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("SELECT ISNULL(SUM(OnHand),0) AS OnStock FROM OITW  WHERE  ItemCode='" & itemCode & "'", dbType)
            If ds IsNot Nothing AndAlso ds.Tables.Count AndAlso ds.Tables(0).Rows.Count > 0 Then
                row("OnStock") = ds.Tables(0).Rows(0)("OnStock")
            End If
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("SELECT ISNULL(SUM(Quantity),0) AS Ordered FROM RDR1 WHERE  ItemCode='" & itemCode & "'", dbType)
            If ds IsNot Nothing AndAlso ds.Tables.Count AndAlso ds.Tables(0).Rows.Count > 0 Then
                row("Ordered") = ds.Tables(0).Rows(0)("Ordered")
            End If
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("SELECT ISNULL(SUM(Quantity),0) AS Shipped FROM INV1 WHERE  ItemCode='" & itemCode & "'", dbType)
            If ds IsNot Nothing AndAlso ds.Tables.Count AndAlso ds.Tables(0).Rows.Count > 0 Then
                row("Shipped") = ds.Tables(0).Rows(0)("Shipped")
            End If
            row("Total") = Double.Parse(row("Ordered")) + Double.Parse(row("OnStock")) - Double.Parse(row("Received"))
            tbFormat.Rows.Add(row)
            dsReturn.Tables.Add(tbFormat)
            Return dsReturn
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    <WebMethod()> _
    Public Function GetOldItemCode(ByVal dbType As String, ByVal itemCode As String) As DataSet
        Try
            Dim dsReturn As New DataSet("OLDITEMCODE")
            Dim connect As New Connection()
            connect.setDB(dbType)
            Dim dsItem As DataSet = connect.ObjectGetAll_Query("SELECT * FROM [@OLDITEMCODE] WHERE U_Itemcode='" & itemCode & "'", dbType)
            dsItem.Tables(0).TableName = "OLDITEMCODE"
            dsReturn.Tables.Add(dsItem.Tables(0).Copy())
            Return dsReturn
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function GetVatGroupByCategory(ByVal dbType As String, ByVal Category As String) As DataSet
        Try
            Dim ds As New DataSet("OVTG")
            Dim strCondition As String = String.Empty
            strCondition = " where Account <> ''"
            If String.IsNullOrEmpty(Category) = False Then
                strCondition = strCondition + " and Category = '" & Category & "'"
            End If
            Dim connect As New Connection()
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query("Select Code, Name, Rate from OVTG " & strCondition & " order by Code", dbType)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function GetReports_DS(ByVal storedName As String, ByVal ParamArrays As DataSet, ByVal dbType As String) As DataSet
        Try
            Dim ds As New DataSet()
            Dim connect As New Connection()
            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Stored_SAP(storedName, ParamArrays, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function LoadCustomItem(ByVal dealID As String, ByVal dbType As String, ByVal department As String) As DataSet
        Try
            Dim b As New SAP_Functions
            Dim connect As New Connection()
            Dim ds As New DataSet("OITM")
            Dim errMsg = connect.setDB(dbType)
            If errMsg.Length = 0 Then
                Dim query = "  SELECT DISTINCT T0.DocEntry, T1.OnHand, T4.WhsCode,T4.WhsName,T3.ListName, T2.Currency,T0.FrgnName, T0.ItemCode,T0.ItemName,T2.Price,T2.PriceList "
                query += " FROM OITM T0 JOIN OITW T1 ON T0.ItemCode  =  T1.ItemCode "
                query += " JOIN ITM1 T2 ON T0.ItemCode = T2.ItemCode "
                query += " JOIN OPLN T3 ON T2.PriceList = T3.ListNum"
                query += " JOIN OWHS T4 ON (T0.U_DealerID = T4.U_DealerID  AND T4.WhsCode = T1.WhsCode)"
                query += " WHERE T4.U_Department = 'PAR' AND  T2.PriceList = 2 AND  T0.U_DealerID = '" & dealID & " ' "
                ds = connect.ObjectGetAll_Query(query, dbType)
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function CheckExistSONunber(ByVal soNumber As String, ByVal dbType As String, ByVal department As String) As DataSet
        Try
            Dim b As New SAP_Functions
            Dim connect As New Connection()
            Dim ds As New DataSet("ORDR")
            Dim errMsg = connect.setDB(dbType)
            If errMsg.Length = 0 Then
                Dim query = "SELECT U_SONBR FROM ORDR WHERE DocStatus = 'O' AND U_SONBR = '" & soNumber & "' AND U_Department = '" & department & "'"
                ds = connect.ObjectGetAll_Query(query, dbType)
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetOpenPO(ByVal cardCode As String, ByVal dbType As String, ByVal department As String) As DataSet
        Try
            Dim b As New SAP_Functions
            Dim connect As New Connection()
            Dim ds As New DataSet("OPOR")
            Dim errMsg = connect.setDB(dbType)
            If errMsg.Length = 0 Then
                Dim query = "SELECT SUM(DocTotal) AS Total FROM OPOR WHERE DocStatus = 'O' AND CardCode = '" & cardCode & "' AND U_Department = '" & department & "'"
                ds = connect.ObjectGetAll_Query(query, dbType)
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetDeposit(ByVal cardCode As String, ByVal dbType As String, ByVal department As String) As DataSet
        Try
            Dim b As New SAP_Functions
            Dim connect As New Connection()
            Dim ds As New DataSet("ORDR")
            Dim errMsg = connect.setDB("VSM")
            If errMsg.Length = 0 Then
                Dim query = "SELECT T1.[Id],[CardCode] ,[Remarks] ,[Dealer_ID] ,[U_Department],"
                query += " T0.[Id] AS DetailID ,[HeaderID],[DepositAmt],[DepositLimit],[DepositDate] ,[IsActive] "
                query += " FROM U_Deposit_Detail T0 JOIN U_Deposit_Header T1 ON T0.HeaderID = T1.ID  WHERE T1.CardCode = '" & cardCode & "' AND U_Department = '" & department & "'"
                query += " ORDER BY DepositDate DESC"
                ds = connect.ObjectGetAll_Query(query, dbType)
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetDepositActive(ByVal cardCode As String, ByVal dbType As String, ByVal department As String) As DataSet
        Try
            Dim b As New SAP_Functions
            Dim connect As New Connection()
            Dim ds As New DataSet("ORDR")
            Dim errMsg = connect.setDB("VSM")
            If errMsg.Length = 0 Then
                Dim query = "SELECT T1.[Id],[CardCode] ,[Remarks] ,[Dealer_ID] ,[U_Department],"
                query += " T0.[Id] AS DetailID ,[HeaderID],[DepositAmt],[DepositLimit],[DepositDate] ,[IsActive] "
                query += " FROM U_Deposit_Detail T0 JOIN U_Deposit_Header T1 ON T0.HeaderID = T1.ID  "
                query += "  WHERE T1.CardCode = '" & cardCode & "' AND U_Department = '" & department & "' AND IsActive = 1 AND T1.CardCode = '" & cardCode & "'"
                query += " ORDER BY DepositDate DESC"
                ds = connect.ObjectGetAll_Query(query, dbType)
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetAllDepositActive(ByVal dbType As String, ByVal department As String) As DataSet
        Try
            Dim b As New SAP_Functions
            Dim connect As New Connection()
            Dim ds As New DataSet("ORDR")
            Dim errMsg = connect.setDB("VSM")
            If errMsg.Length = 0 Then
                Dim query = "SELECT T2.CardName,T1.[Id],T1.[CardCode] ,[Remarks] ,[Dealer_ID] ,[U_Department],"
                query += " T0.[Id] AS DetailID ,[HeaderID],[DepositAmt],[DepositLimit],[DepositDate] ,[IsActive] "
                query += " FROM U_Deposit_Detail T0 JOIN U_Deposit_Header T1 ON T0.HeaderID = T1.ID  "
                query += " JOIN OCRD T2 ON T1.CardCode = T2.CardCode  "
                query += "  WHERE  U_Department = '" & department & "' AND IsActive = 1 "
                query += " ORDER BY DepositDate DESC"
                ds = connect.ObjectGetAll_Query(query, dbType)
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetTblRDR1RemainAmntByCustomer(ByVal dbType As String, ByVal ID_Dealer As String, ByVal cardCode As String) As DataSet
        Try
            Dim str As String = String.Empty
            Dim connect As New Connection()
            connect.setDB(dbType)
            str = "Select ROW_NUMBER() Over(Order By DocEntry) No, U_Discount, U_IncentiveDiscount, VatGroup, VatPrcnt, VatSum, U_BaseLine, "
            str += " 0 Selected, DocEntry, LineNum, NumAtCard, U_Color, U_Model, U_Product, ItemCode, Dscription, Quantity, PriceBefDi, DiscPrcnt, Price, LineTotal, ShipDate, 0 Paid, Remain, internal_id, "
            str += " (1.00 * (isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0)) + ( (isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0)) * isNull(VatPrcnt, 0) /100)) As SumApplied, "
            str += " Remain As Remain_Org, CardCode from tbl_RDR1 "
            str += " where CardCode = '" + cardCode + "' and Remain > 0 and DocEntry = ( select Max(DocEntry) from tbl_RDR1  where CardCode = '" + cardCode + "' ) "
            Return connect.ObjectGetAll_Query(str, dbType)
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    <WebMethod()> _
    Public Function GetTblRDR1ByCustomer(ByVal dbType As String, ByVal ID_Dealer As String, ByVal cardCode As String, ByVal department As String) As DataSet
        Try
            Dim str As String = String.Empty
            Dim connect As New Connection()
            connect.setDB(dbType)
            str = "   Select ROW_NUMBER() Over(Order By T1.DocEntry) No, 0 Selected, *, LineNum as U_BaseLine, "
            str += "  (1.00 * ( isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0) ) + ( (isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0)) * isNull(VatPrcnt, 0) /100)) As SumApplied, 0.00 Paid, "
            str += "  ( (isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0) ) + ( (isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0)) * isNull(VatPrcnt, 0) /100)) As Remain, "
            str += "  ( (isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0)) + ( (isNull(LineTotal, 0) - isNull(U_Discount, 0) - isNull(U_IncentiveDiscount, 0)) * isNull(VatPrcnt, 0) /100)) As Remain_Org, '' CardCode "
            str += "  from RDR1 T0 JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry "
            str += "  WHERE T1.CardCode ='" + cardCode + "' AND U_Department ='" + department + "'"
            Return connect.ObjectGetAll_Query(str, dbType)
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function UpdateDeposit(ByVal data As DataSet, ByVal dbType As String) As String
        Dim connect As New Connection()
        Try
            Dim errMsg As String = connect.setDB(dbType)
            For Each row As DataRow In data.Tables("U_Deposit_Header").Rows
                If row("ID").ToString().Length = 0 Then
                    If errMsg.Length = 0 Then
                        Dim params = New Object() {row("CardCode"), row("Remarks"), row("Dealer_ID"), row("U_Department")}
                        Dim query As String = String.Empty
                        'Insert Header
                        query = "INSERT INTO U_Deposit_Header(CardCode,Remarks,Dealer_ID,U_Department)"
                        query += " VALUES(@Param1,@Param2,@Param3,@Param4)"
                        Dim count As Integer = connect.Object_Execute(query, params, dbType)
                        'Get Max Id
                        Dim ds As DataSet = connect.ObjectGetAll_Query("SELECT IDENT_CURRENT('U_Deposit_Header')", dbType)
                        'Insert Detail
                        If ds IsNot Nothing And ds.Tables.Count > 0 Then
                            Dim tbDetail As DataTable = data.Tables("U_Deposit_Detail")
                            Dim masterId As Integer = Integer.Parse(ds.Tables(0).Rows(0)(0).ToString())
                            If tbDetail IsNot Nothing Then
                                If tbDetail.Rows.Count > 0 Then
                                    For Each rowDetail As DataRow In tbDetail.Rows
                                        'Get Max ID
                                        query = "INSERT INTO U_Deposit_Detail(HeaderID,DepositAmt,DepositLimit,DepositDate,IsActive)"
                                        query += " VALUES(@Param1,@Param2,@Param3,@Param4,@Param5)"
                                        params = New Object() {masterId, rowDetail("DepositAmt"), rowDetail("DepositLimit"), rowDetail("DepositDate"), rowDetail("IsActive")}
                                        count = connect.Object_Execute(query, params, dbType)
                                        If count = 0 Then
                                            'Delete Header if have any error
                                            query = "DELETE FROM U_Deposit_Header "
                                            query += " WHERE ID=" + masterId
                                            connect.Object_Execute(query, dbType)
                                            Return "Can not update MRP Detail."
                                        End If
                                    Next
                                Else
                                    'Delete Header if have any error
                                    query = "DELETE FROM U_Deposit_Header "
                                    query += " WHERE ID=" + masterId
                                    connect.Object_Execute(query, dbType)
                                    Return "Can not update MRP Detail."
                                End If
                            End If
                        End If
                    End If
                Else
                    If errMsg.Length = 0 Then
                        Dim params = New Object() {row("Remarks"), row("ID")}
                        Dim query As String = String.Empty
                        query = "UPDATE  U_Deposit_Header SET Remarks = @Param1"
                        query += " WHERE ID = @Param2 "
                        Dim count As Integer = connect.Object_Execute(query, params, dbType)
                        Dim tbDetail As DataTable = data.Tables("U_Deposit_Detail")
                        If tbDetail IsNot Nothing Then
                            If tbDetail.Rows.Count > 0 Then
                                For Each rowDetail As DataRow In tbDetail.Rows
                                    If rowDetail("ID").ToString().Length > 0 Then
                                        query = "UPDATE  U_Deposit_Detail SET DepositAmt = @Param1,DepositDate = @Param2,IsActive = @Param3, DepositLimit = @Param4"
                                        query += " WHERE ID = @Param5"
                                        params = New Object() {rowDetail("DepositAmt"), rowDetail("DepositDate"),
                                                                   rowDetail("IsActive"), rowDetail("DepositLimit"), rowDetail("ID")}
                                        count = connect.Object_Execute(query, params, dbType)
                                    Else
                                        query = "INSERT INTO U_Deposit_Detail(HeaderID,DepositAmt,DepositLimit,DepositDate,IsActive)"
                                        query += " VALUES(@Param1,@Param2,@Param3,@Param4,@Param5)"
                                        params = New Object() {row("ID"), rowDetail("DepositAmt"), rowDetail("DepositLimit"), rowDetail("DepositDate"), rowDetail("IsActive")}
                                        count = connect.Object_Execute(query, params, dbType)
                                    End If
                                Next
                            End If
                        End If
                    End If
                End If
                Return errMsg
            Next
        Catch ex As Exception
            Return ex.Message
        End Try
        Return String.Empty
    End Function
    <WebMethod()> _
    Public Function UpdateFreight(ByVal data As DataSet, ByVal dbType As String) As String
        Dim connect As New Connection()
        Try
            Dim errMsg As String = connect.setDB(dbType)
            For Each row As DataRow In data.Tables("U_Part_Fee").Rows
                If row("ID").ToString().Length = 0 Then
                    If errMsg.Length = 0 Then
                        Dim params = New Object() {row("FeeName"), row("FeeValue"), row("SQDocEntry"), row("DealerID"), row("Department"), row("Comments")}
                        Dim query As String = String.Empty
                        'Insert Header
                        query = "INSERT INTO U_Part_Fee(FeeName,FeeValue,SQDocEntry,DealerID,Department,Comments,CreateDate)"
                        query += " VALUES(@Param1,@Param2,@Param3,@Param4,@Param5,@Param6,GETDATE())"
                        Dim count As Integer = connect.Object_Execute(query, params, dbType)
                    End If
                Else
                    If errMsg.Length = 0 Then
                        Dim params = New Object() {row("FeeValue"), row("Comments"), row("ID")}
                        Dim query As String = String.Empty
                        query = "UPDATE  U_Part_Fee SET FeeValue = @Param1,Comments = @Param2"
                        query += " WHERE ID = @Param3 "
                        Dim count As Integer = connect.Object_Execute(query, params, dbType)
                    End If
                End If
            Next
        Catch ex As Exception
            Return ex.Message
        End Try
        Return String.Empty
    End Function
    <WebMethod()> _
    Public Function GetConfig(ByVal dbType As String, ByVal customerCode As String) As DataSet
        Try
            Dim ds As New DataSet("U_Config")
            Dim connect As New Connection()
            Dim str As String = String.Empty
            str = "SELECT * FROM U_Config WHERE CardCode = '" & customerCode & "'"
            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function UpdateConfig(ByVal data As DataSet, ByVal dbType As String) As String
        Dim connect As New Connection()
        Try
            Dim errMsg As String = connect.setDB(dbType)
            For Each row As DataRow In data.Tables("U_Config").Rows
                If row("ID").ToString().Length = 0 Then
                    If errMsg.Length = 0 Then
                        Dim params = New Object() {row("CardCode"), row("OC"), row("LT"), row("MRPA"), row("MRPB"), row("MRPC"), row("MRPD"), row("Series")}
                        Dim query As String = String.Empty
                        'Insert Header
                        query = "INSERT INTO U_Config(CardCode,OC,LT,MRPA,MRPB,MRPC,MRPD,Series,UpdateDate)"
                        query += " VALUES(@Param1,@Param2,@Param3,@Param4,@Param5,@Param6,@Param7,@Param8,GETDATE())"
                        Dim count As Integer = connect.Object_Execute(query, params, dbType)
                    End If
                Else
                    If errMsg.Length = 0 Then
                        Dim params = New Object() {row("OC"), row("LT"), row("MRPA"), row("MRPB"), row("MRPC"), row("MRPD"), row("Series"), row("CardCode")}
                        Dim query As String = String.Empty
                        query = "UPDATE  U_Config SET OC = @Param1,LT = @Param2,MRPA = @Param3,MRPB = @Param4,MRPC = @Param5,MRPD = @Param6,Series = @Param7"
                        query += " WHERE CardCode = @Param8 "
                        Dim count As Integer = connect.Object_Execute(query, params, dbType)
                    End If
                End If
            Next
        Catch ex As Exception
            Return ex.Message
        End Try
        Return String.Empty
    End Function
    <WebMethod()> _
    Public Function UpdateSeriesConfig(ByVal data As DataSet, ByVal dbType As String) As String
        Dim connect As New Connection()
        Try
            Dim errMsg As String = connect.setDB(dbType)
            For Each row As DataRow In data.Tables("U_Config").Rows
                If row("ID").ToString().Length = 0 Then
                    If errMsg.Length = 0 Then
                        Dim params = New Object() {row("CardCode"), row("Series")}
                        Dim query As String = String.Empty
                        'Insert Header
                        query = "INSERT INTO U_Config(CardCode,Series,UpdateDate)"
                        query += " VALUES(@Param1,@Param2,GETDATE())"
                        Dim count As Integer = connect.Object_Execute(query, params, dbType)
                    End If
                Else
                    If errMsg.Length = 0 Then
                        Dim params = New Object() {row("Series"), row("CardCode")}
                        Dim query As String = String.Empty
                        query = "UPDATE  U_Config SET Series = @Param1"
                        query += " WHERE CardCode = @Param2 "
                        Dim count As Integer = connect.Object_Execute(query, params, dbType)
                    End If
                End If
            Next
        Catch ex As Exception
            Return ex.Message
        End Try
        Return String.Empty
    End Function
    <WebMethod()> _
    Public Function GetInventoryTransferDraft(ByVal dbType As String, ByVal ID_Dealer As String, ByVal customerCode As String, ByVal dealerCode As String, _
                                              ByVal debitNote As String, ByVal fromDate As Date, ByVal todate As Date, ByVal objType As String) As DataSet
        Try
            Dim ds As New DataSet("ODRF")
            Dim connect As New Connection()
            Dim str As String = String.Empty
            str = "SELECT * FROM ODRF WHERE DocStatus = 'O' AND ObjType = " & objType & " AND U_Department = 'PAR' "
            If dealerCode.Length > 0 Then
                str += " AND CardCode LIKE '%" & dealerCode & "%' OR CardName LIKE  '%" & dealerCode & "%'"
            End If
            If debitNote.Length > 0 Then
                str += " AND U_DebitNote = '" & debitNote & "'"
            End If
            If customerCode.Length > 0 Then
                str += "AND U_CustomerID = '" & customerCode & "'"
            End If
            str += " AND DocDate BETWEEN @Param1 AND @Param2 "
            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, New Object() {fromDate, todate, customerCode}, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetDraftMarketingDocument_ReturnDS(ByVal keyValue As String, ByVal dbType As String, ByVal keyName As String) As DataSet
        Try
            Dim sStr As String = ""

            Dim connect As New Connection()
            Dim HeaderTableName As String = "ODRF"
            Dim LineTableName1 As String = "DRF1"
            Dim LineTableName2 As String = ""

            Dim ds As New DataSet("Document")
            Dim dt1 As New DataTable
            connect.setDB(dbType)
            Dim str As String
            str = "SELECT ROW_NUMBER() Over(Order By " + keyName + ") No,* FROM " + HeaderTableName + " WHERE DocStatus = 'O' AND U_Department = 'PAR' AND " + keyName + "='" + keyValue + "' "

            dt1 = connect.ObjectGetAll_Query(str, dbType).Tables(0)

            dt1.TableName = HeaderTableName
            ds.Tables.Add(dt1.Copy)
            If KeyName = "DocEntry" Then
                If LineTableName1 <> "" Then
                    Dim dt2 As New DataTable
                    connect.setDB(dbType)
                    Dim str1 As String = ""
                    str1 = "SELECT ROW_NUMBER() Over(Order By " + KeyName + ") No, isNull(T1.SysNumber, -1) SysNumber, isNull(T1.DistNumber, '') DistNumber, isNull(T1.InDate, '01/01/1900') InDate, isNull(T1.Notes, '') Notes, * from " + LineTableName1 + " left outer join SRI1_LINK T0  on T0.BaseEntry = " + LineTableName1 + ".DocEntry and T0.ItemCode = " + LineTableName1 + ".ItemCode and T0.WhsCode = " + LineTableName1 + ".WhsCode" _
                        & " LEFT JOIN OSRN T1 on T1.SysNumber = T0.SysSerial and T1.ItemCode = T0.ItemCode  WHERE " + KeyName + "='" + keyValue + "'"
                    dt2 = connect.ObjectGetAll_Query(str1, dbType).Tables(0)
                    dt2.TableName = LineTableName1
                    ds.Tables.Add(dt2.Copy)
                End If
            End If
            Return ds
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

#Region "MRP DELEAR"
    <WebMethod()> _
    Public Function GetMRP(ByVal dbType As String) As DataSet
        Try
            Dim ds As New DataSet("OFCT")
            Dim connect As New Connection()

            Dim str As String = ""
            str = "SELECT * FROM OFCT"

            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Utils.WriteLog(ex.Message + "-" + ex.StackTrace)
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetScenario(ByVal dbType As String, ByVal dealerId As String) As DataSet
        Try
            Dim ds As New DataSet("OFCT")
            Dim connect As New Connection()

            Dim str As String = ""
            str = "SELECT ScenarioName,ScenarioDesc FROM U_MRP_Header GROUP BY  ScenarioName,ScenarioDesc "

            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Utils.WriteLog(ex.Message + "-" + ex.StackTrace)
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetMRPByAbsID(ByVal dbType As String, ByVal absID As String,
                                  ByVal startDate As Date, ByVal endDate As Date, ByVal deliveryDate As Date,ByVal whsCode As String) As DataSet
        Try
            Dim ds As New DataSet("OFCT")
            Dim connect As New Connection()

            Dim str As String = ""
            str += " SELECT ROW_NUMBER() Over(Order By T0.U_ModelSales) No,FC.ForeCast,SUM(T3.OnHand) AS InHand, SUM(MinStock) AS MinStock,T0.U_ModelSales,T0.U_ProductSales,T0.CardCode,T4.CardName,T1.Name AS ModelName, T2.Name AS ProductName"
            str += " FROM  OITM T0"
            str += " JOIN [@MODELSALES] T1 ON T1.Code = T0.U_ModelSales"
            str += "  JOIN [@PRODUCTSALES] T2 ON T2.Code = T0.U_ProductSales"
            str += " JOIN OITW T3 ON T0.ItemCode = T3.ItemCode"
            str += "  JOIN OCRD T4 ON T0.CardCode = T4.CardCode"
            str += " JOIN ("
            str += " SELECT  T0.U_ModelSales, T0.U_ProductSales,SUM(T5.Quantity) AS ForeCast "
            str += " FROM FCT1 T5 JOIN OITM T0 ON T5.ItemCode = T0.ItemCode"
            str += "  WHERE T5.Quantity >=1 AND Date BETWEEN @Param1 AND @Param2 AND T5.AbsID = @Param3"
            str += " GROUP BY T0.U_ModelSales, T0.U_ProductSales) AS FC ON (FC.U_ModelSales = T0.U_ModelSales AND FC.U_ProductSales = T0.U_ProductSales) "
            str += "  WHERE T3.WhsCode IN (@Param4) "
            str += " GROUP BY FC.ForeCast,T0.U_ModelSales,T0.U_ProductSales,T0.CardCode,T1.Name, T2.Name,T4.CardName"


            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, New Object() {startDate, endDate, absID, whsCode}, dbType)
            If ds IsNot Nothing AndAlso ds.Tables.Count > 0 Then

                Dim dsSO As DataSet = Me.GetSOMRP(dbType, startDate, deliveryDate, whsCode)

                Dim dsPO As DataSet = Me.GetPOMRP(dbType, startDate, deliveryDate, whsCode)
                ds.Tables(0).Columns.Add("SOQty")
                ds.Tables(0).Columns.Add("POQty")
                For Each r As DataRow In ds.Tables(0).Rows

                    If dsSO IsNot Nothing AndAlso dsSO.Tables.Count > 0 Then
                        Dim row() As DataRow = dsSO.Tables(0).Select("U_ModelSales='" & r("U_ModelSales") & "' AND U_ProductSales ='" & r("U_ProductSales") & "'")
                        If row.Length > 0 Then
                            r("SOQty") = row(0)("SOQty")
                        End If
                    End If
                    If dsPO IsNot Nothing AndAlso dsPO.Tables.Count > 0 Then
                        Dim row() As DataRow = dsPO.Tables(0).Select("U_ModelSales='" & r("U_ModelSales") & "' AND U_ProductSales ='" & r("U_ProductSales") & "'")
                        If row.Length > 0 Then
                            r("POQty") = row(0)("POQty")
                        End If
                    End If
                Next
            End If

            Return ds
        Catch ex As Exception
            Utils.WriteLog(ex.Message + "-" + ex.StackTrace)
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetSOMRP(ByVal dbType As String, ByVal startDate As Date, ByVal endDate As Date, ByVal whsCode As String) As DataSet
        Try
            Dim ds As New DataSet("ORDR")
            Dim connect As New Connection()

            Dim str As String = ""
            str = "  SELECT T2.U_ModelSales,T2.U_ProductSales,SUM(T1.OpenCreQty) AS SOQty  "
            str += " FROM ORDR T0 JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry "
            str += " JOIN OITM T2 ON T1.ItemCode = T2.ItemCode"
            str += " WHERE T0.DocDueDate BETWEEN @Param1 AND @Param2 AND U_Department ='SAL' AND T1.WhsCode IN (@Param3)"
            str += " GROUP BY T2.U_ModelSales,T2.U_ProductSales"

            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, New Object() {startDate, endDate, whsCode}, dbType)

            Return ds
        Catch ex As Exception
            Utils.WriteLog(ex.Message + "-" + ex.StackTrace)
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetPOMRP(ByVal dbType As String, ByVal startDate As Date, ByVal endDate As Date, ByVal whsCode As String) As DataSet
        Try
            Dim ds As New DataSet("ORDR")
            Dim connect As New Connection()

            Dim str As String = ""
            str = " SELECT T2.U_ModelSales,T2.U_ProductSales,SUM(T1.OpenCreQty) AS POQty  "
            str += " FROM OPOR T0 JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry "
            str += " JOIN OITM T2 ON T1.ItemCode = T2.ItemCode"
            str += " WHERE T0.DocDueDate BETWEEN @Param1 AND @Param2 AND U_Department ='SAL' AND T1.WhsCode IN (@Param3)"
            str += "   GROUP BY T2.U_ModelSales,T2.U_ProductSales"

            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, New Object() {startDate, endDate, whsCode}, dbType)

            Return ds
        Catch ex As Exception
            Utils.WriteLog(ex.Message + "-" + ex.StackTrace)
            Throw ex
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function CheckMRPScenario(ByVal dbType As String, ByVal scenarioName As String) As DataSet
        Try
            Dim ds As New DataSet("ORDR")
            Dim connect As New Connection()

            Dim str As String = ""
            str = " SELECT *  "
            str += " FROM U_MRP_Header  "
            str += " WHERE ScenarioName  = @Param1"
            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, New Object() {scenarioName}, dbType)
            Return ds
        Catch ex As Exception
            Utils.WriteLog(ex.Message + "-" + ex.StackTrace)
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function UpdateMRP(ByVal data As DataSet, ByVal dbType As String) As String
        Dim connect As New Connection()
        Try
            Dim errMsg As String = connect.setDB(dbType)
            For Each row As DataRow In data.Tables("U_MRP_Header").Rows
                Dim dsCheckSC As DataSet = Me.CheckMRPScenario(dbType, row("ScenarioName"))
                If dsCheckSC IsNot Nothing AndAlso dsCheckSC.Tables.Count > 0 AndAlso dsCheckSC.Tables(0).Rows.Count = 0 Then
                    If errMsg.Length = 0 Then
                        Dim params = New Object() {row("RunDate"), row("FromDate"), row("ToDate"), row("AbsID"),
                                                   row("ForeCastCode"), row("Remarks"), row("ScenarioName"), row("ScenarioDesc"), row("DealerId"), row("U_Department"), row("WhsCode")}
                        Dim query As String = String.Empty
                        'Insert Header
                        query = "INSERT INTO U_MRP_Header(RunDate,FromDate,ToDate,AbsID,ForeCastCode,Remarks,ScenarioName,ScenarioDesc,DealerId,U_Department,WhsCode)"
                        query += " VALUES(@Param1,@Param2,@Param3,@Param4,@Param5,@Param6,@Param7,@Param8,@Param9,@Param10,@Param11)"
                        Dim count As Integer = connect.Object_Execute(query, params, dbType)
                        'Get Max Id
                        Dim ds As DataSet = connect.ObjectGetAll_Query("SELECT IDENT_CURRENT('U_MRP_Header')", dbType)
                        'Insert Detail
                        If ds IsNot Nothing And ds.Tables.Count > 0 Then
                            Dim tbDetail As DataTable = data.Tables("U_MRP_Detail")
                            Dim masterId As Integer = Integer.Parse(ds.Tables(0).Rows(0)(0).ToString())
                            If tbDetail IsNot Nothing Then
                                If tbDetail.Rows.Count > 0 Then
                                    For Each rowDetail As DataRow In tbDetail.Rows
                                        'Get Max ID
                                        query = "INSERT INTO U_MRP_Detail(HeaderID,U_ModelSales,U_ProductSales,VendorCode,ForeCastQty,MinStock,SOQty,POQty,InHand,MRP)"
                                        query += " VALUES(@Param1,@Param2,@Param3,@Param4,@Param5,@Param6,@Param7,@Param8,@Param9,@Param10)"
                                        params = New Object() {masterId, rowDetail("U_ModelSales"), rowDetail("U_ProductSales"), rowDetail("VendorCode"), rowDetail("ForeCastQty"),
                                                               rowDetail("MinStock"), rowDetail("SOQty"), rowDetail("POQty"), rowDetail("InHand"), rowDetail("MRP")}
                                        count = connect.Object_Execute(query, params, dbType)
                                        If count = 0 Then
                                            'Delete Header if have any error
                                            query = "DELETE FROM U_MRP_Header "
                                            query += " WHERE ID=" + masterId
                                            connect.Object_Execute(query, dbType)
                                            Return "Can not update MRP Detail."
                                        End If
                                    Next
                                Else
                                    'Delete Header if have any error
                                    query = "DELETE FROM U_MRP_Header "
                                    query += " WHERE ID=" + masterId
                                    connect.Object_Execute(query, dbType)
                                    Return "Can not update MRP Detail."
                                End If
                            End If
                        End If
                    End If
                Else
                    If errMsg.Length = 0 Then
                        Dim params = New Object() {row("RunDate"), row("FromDate"), row("ToDate"), row("AbsID"),
                                                   row("ForeCastCode"), row("Remarks"), row("ScenarioDesc"), dsCheckSC.Tables(0).Rows(0)("ID")}
                        Dim query As String = String.Empty
                        query = "UPDATE  U_MRP_Header SET RunDate = @Param1,FromDate=@Param2,ToDate=@Param3,AbsID=@Param4,ForeCastCode=@Param5,Remarks=@Param6,ScenarioDesc=@Param7"
                        query += " WHERE ID = @Param8 "
                        Dim count As Integer = connect.Object_Execute(query, params, dbType)
                        Dim tbDetail As DataTable = data.Tables("U_MRP_Detail")
                        If tbDetail IsNot Nothing Then
                            If tbDetail.Rows.Count > 0 Then
                                For Each rowDetail As DataRow In tbDetail.Rows
                                    query = "UPDATE  U_MRP_Detail SET VendorCode = @Param2,ForeCastQty = @Param3,MinStock = @Param4,SOQty =@Param5,POQty = @Param6,InHand = @Param7,MRP = @Param8"
                                    query += " WHERE HeaderID = @Param9"
                                    params = New Object() {rowDetail("VendorCode"), rowDetail("ForeCastQty"),
                                                               rowDetail("MinStock"), rowDetail("SOQty"), rowDetail("POQty"), rowDetail("InHand"), rowDetail("MRP"), dsCheckSC.Tables(0).Rows(0)("ID")}
                                    count = connect.Object_Execute(query, params, dbType)
                                Next
                            End If
                        End If
                    End If
                End If
                Return errMsg
            Next
        Catch ex As Exception
            Return ex.Message
        End Try
        Return String.Empty
    End Function
    <WebMethod()> _
    Public Function UpdatePOMRP(ByVal data As DataSet, ByVal dbType As String) As String
        Dim connect As New Connection()
        Try
            For Each row As DataRow In data.Tables("U_MRP_POHeader").Rows
                Dim errMsg As String = connect.setDB(dbType)
                If errMsg.Length = 0 Then
                    Dim params = New Object() {row("Series"), row("DocStatus"), row("DocDate"), row("DocDueDate"),
                                               row("TaxDate"), row("CardCode"), row("CardName"), row("Comments"), row("U_SOType"), "Open", row("DocTotal"), row("ScenarioCode"), row("DealerId"), row("U_Department"), row("WhsCode")}
                    Dim query As String = String.Empty
                    'Insert Header
                    query = "INSERT INTO U_MRP_POHeader(Series,DocStatus,DocDate,DocDueDate,TaxDate,CardCode,CardName,Comments,U_SOType,Status,DocTotal,ScenarioCode,DealerId,U_Department,WhsCode)"
                    query += " VALUES(@Param1,@Param2,@Param3,@Param4,@Param5,@Param6,@Param7,@Param8,@Param9,@Param10,@Param11,@Param12,@Param13,@Param14,@Param15)"
                    Dim count As Integer = connect.Object_Execute(query, params, dbType)
                    'Get Max Id
                    Dim ds As DataSet = connect.ObjectGetAll_Query("SELECT IDENT_CURRENT('U_MRP_POHeader')", dbType)
                    'Insert Detail
                    If ds IsNot Nothing And ds.Tables.Count > 0 Then
                        Dim tbDetail As DataTable = data.Tables("U_MRP_PODetail")
                        Dim masterId As Integer = Integer.Parse(ds.Tables(0).Rows(0)(0).ToString())
                        If tbDetail IsNot Nothing Then
                            If tbDetail.Rows.Count > 0 Then
                                For Each rowDetail As DataRow In tbDetail.Rows
                                    'Get Max ID
                                    query = "INSERT INTO U_MRP_PODetail(HeaderID,LineStatus,U_ModelSales,U_ProductSales,UomCode,Quantity,PriceBefDi,DiscPrcnt,Price,LineTotal,OrderQty)"
                                    query += " VALUES(@Param1,@Param2,@Param3,@Param4,@Param5,@Param6,@Param7,@Param8,@Param9,@Param10,@Param11)"
                                    params = New Object() {masterId, rowDetail("LineStatus"), rowDetail("U_ModelSales"), rowDetail("U_ProductSales"),
                                                           rowDetail("UomCode"), rowDetail("Quantity"),
                                                          rowDetail("PriceBefDi"), rowDetail("DiscPrcnt"), rowDetail("Price"), rowDetail("LineTotal"), rowDetail("OrderQty")}
                                    count = connect.Object_Execute(query, params, dbType)
                                    If count = 0 Then
                                        'Delete Header if have any error
                                        query = "DELETE FROM U_MRP_POHeader "
                                        query += " WHERE ID=" + masterId
                                        connect.Object_Execute(query, dbType)
                                        Return "Can not update PO Detail."
                                    End If
                                Next
                            Else
                                'Delete Header if have any error
                                query = "DELETE FROM U_MRP_POHeader "
                                query += " WHERE ID=" + masterId
                                connect.Object_Execute(query, dbType)
                                Return "Can not update PO Detail."
                            End If
                        End If
                    End If
                End If
                Return errMsg
            Next
        Catch ex As Exception
            Return ex.Message
        End Try
        Return String.Empty
    End Function
    <WebMethod()> _
    Public Function GetMRPPOList(ByVal dbType As String, ByVal ID_Dealer As String, ByVal CardCode As String, _
                                     ByVal FromDocDate As Date, ByVal ToDocDate As Date, ByVal FromDueDate As Date, ByVal ToDueDate As Date, _
                                    ByVal FromDoctotal As String, ByVal ToDoctotal As String, ByVal Status As String, ByVal scenarioCode As String) As DataSet
        Dim ds As DataSet
        Dim connect As New Connection()
        connect.setDB(dbType)

        Dim str As String
        Dim condition As String = String.Empty

        str = "SELECT DISTINCT T4.Name AS ProductName, T5.Name AS ModelName, DATEADD(DAY, ISNULL(OITM.LeadTime,0) , U_MRP_POHeader.DocDate ) AS DocDueLeadDate, U_MRP_POHeader.*,U_MRP_PODetail.*"
        str += " FROM U_MRP_POHeader JOIN U_MRP_PODetail ON U_MRP_POHeader.Id = U_MRP_PODetail.HeaderID"
        str += " JOIN OITM ON (OITM.U_ModelSales = U_MRP_PODetail.U_ModelSales AND OITM.U_ProductSales = U_MRP_PODetail.U_ProductSales)"
        str += " JOIN [@MODELSALES] T5 ON T5.Code = U_MRP_PODetail.U_ModelSales"
        str += " JOIN [@PRODUCTSALES] T4 ON T4.Code = U_MRP_PODetail.U_ProductSales"

        ' Build condition to query
        condition = condition + " WHERE convert(varchar(8), DocDate, 112) >= " + FromDocDate.ToString("yyyyMMdd")
        condition = condition + "   AND convert(varchar(8), DocDate, 112) <= " + ToDocDate.ToString("yyyyMMdd")
        condition = condition + "   AND convert(varchar(8), DocDueDate, 112) >= " + FromDueDate.ToString("yyyyMMdd")
        condition = condition + "   AND convert(varchar(8), DocDueDate, 112) <= " + ToDueDate.ToString("yyyyMMdd")

        If (Status.Length > 0 AndAlso Status.ToUpper() <> "A") Then
            condition = condition + "   AND Status = '" + Status + "'"
        End If

        If (CardCode.Length > 0) Then
            condition = condition + "   AND CardCode = '" + CardCode + "'"
        End If

        ' DocTotal condition
        If (String.IsNullOrEmpty(FromDoctotal) = False And String.IsNullOrEmpty(ToDoctotal) = False) Then
            condition = condition + "   AND DocTotal >= " + FromDoctotal + "   AND DocTotal <= " + ToDoctotal
        ElseIf (String.IsNullOrEmpty(FromDoctotal) = False) Then
            condition = condition + "   AND DocTotal >= " + FromDoctotal
        ElseIf (String.IsNullOrEmpty(ToDoctotal) = False) Then
            condition = condition + "   AND DocTotal <= " + ToDoctotal
        End If

        condition = condition + "   AND ScenarioCode = '" + scenarioCode + "'"

        str = str + condition

        ds = connect.ObjectGetAll_Query(str, dbType)
        Return ds

    End Function
    <WebMethod()> _
    Public Function GetMRPList(ByVal dbType As String, ByVal ID_Dealer As String,
                                     ByVal FromDocDate As Date, ByVal ToDocDate As Date, ByVal foreCastCode As String, _
                                     ByVal scenarioName As String) As DataSet
        Dim ds As DataSet
        Dim connect As New Connection()
        connect.setDB(dbType)

        Dim str As String
        Dim condition As String = String.Empty

        str = "SELECT ROW_NUMBER() Over(Order By Id) No,* FROM U_MRP_Header "
        ' Build condition to query
        condition = condition + " WHERE convert(varchar(8), RunDate, 112) >= " + FromDocDate.ToString("yyyyMMdd")
        condition = condition + "   AND convert(varchar(8), RunDate, 112) <= " + ToDocDate.ToString("yyyyMMdd")


        If (foreCastCode.Length > 0) Then
            condition = condition + "   AND ForeCastCode LIKE '%" + foreCastCode + "%'"
        End If

        If (scenarioName.Length > 0) Then
            condition = condition + "   AND ScenarioName LIKE '%" + scenarioName + "%'"
        End If

        str = str + condition

        ds = connect.ObjectGetAll_Query(str, dbType)
        Return ds

    End Function
    <WebMethod()> _
    Public Function GetMRPDetailList(ByVal dbType As String, ByVal ID_Dealer As String) As DataSet
        Dim ds As DataSet
        Dim connect As New Connection()
        connect.setDB(dbType)

        Dim str As String
        Dim condition As String = String.Empty

        str = "SELECT ROW_NUMBER() Over(Order By T0.Id) No,T1.*,T3.CardName,T4.Name AS ProductName,T5.Name AS ModelName "
        str += " FROM U_MRP_Header T0 JOIN U_MRP_Detail T1 ON T0.ID= T1.HeaderID"
        str += " JOIN OCRD T3 ON T1.VendorCode = T3.CardCode"
        str += " JOIN [@MODELSALES] T5 ON T5.Code = T1.U_ModelSales"
        str += " JOIN [@PRODUCTSALES] T4 ON T4.Code = T1.U_ProductSales"
        str += " WHERE T0.DealerId='" + ID_Dealer + "'"

        str = str + condition

        ds = connect.ObjectGetAll_Query(str, dbType)
        Return ds

    End Function

    <WebMethod()> _
    Public Function GetMRPPODataById(ByVal dbType As String, ByVal ID_Dealer As String, ByVal Id As String) As DataSet
        Dim ds As DataSet = New DataSet
        Dim connect As New Connection()
        connect.setDB(dbType)

        Dim str As String
        Dim condition As String = String.Empty
        str = "SELECT ROW_NUMBER() Over(Order By Id) No,* FROM U_MRP_POHeader WHERE ID = " + Id
        Dim dsHeader As DataSet = connect.ObjectGetAll_Query(str, dbType)
        str = "SELECT ROW_NUMBER() Over(Order By U_MRP_PODetail.Id) No,U_MRP_PODetail.[Id],[HeaderID],[LineStatus],[U_ModelSales] AS U_Model,[U_ProductSales] AS U_Product,[UomCode],"
        str += " [PriceBefDi],[DiscPrcnt],[Price],[LineTotal],[TaxCode],[OrderQty],(Quantity - OrderQty) AS Quantity,U_MRP_POHeader.WhsCode,U_MRP_POHeader.U_Department FROM U_MRP_PODetail JOIN U_MRP_POHeader ON U_MRP_PODetail.HeaderID = U_MRP_POHeader.ID WHERE  HeaderID= " + Id
        Dim dsDetail As DataSet = connect.ObjectGetAll_Query(str, dbType)

        If (dsHeader IsNot Nothing AndAlso dsHeader.Tables.Count > 0) Then
            dsHeader.Tables(0).TableName = "U_MRP_POHeader"
            ds.Tables.Add(dsHeader.Tables(0).Copy())
        End If
        If (dsDetail IsNot Nothing AndAlso dsDetail.Tables.Count > 0) Then
            dsHeader.Tables(0).TableName = "U_MRP_PODetail"
            ds.Tables.Add(dsDetail.Tables(0).Copy())
        End If
        Return ds
    End Function
    <WebMethod()> _
    Public Function UpdatePOMRPStatus(ByVal poID As String, ByVal dbType As String, ByVal dsData As DataSet) As String
        Dim connect As New Connection()
        Try
            Dim errMsg As String = connect.setDB(dbType)
            If errMsg.Length = 0 Then
                If dsData IsNot Nothing AndAlso dsData.Tables.Count > 1 Then
                    Dim query As String = String.Empty
                    For Each r As DataRow In dsData.Tables(1).Rows
                        query = "UPDATE U_MRP_PODetail SET OrderQty= @Param1+OrderQty WHERE HeaderID = @Param2 AND U_ModelSales = @Param3 AND U_ProductSales = @Param4"
                        Dim count As Integer = connect.Object_Execute(query, New Object() {r("Quantity"), poID, r("U_Model"), r("U_Product")}, dbType)

                        query = "UPDATE U_MRP_POHeader SET Status = @Param1 WHERE ID = @Param2"
                        count = connect.Object_Execute(query, New Object() {"Open", poID}, dbType)
                    Next
                End If
            End If
            Return errMsg
        Catch ex As Exception
            Return ex.Message
        End Try
        Return String.Empty
    End Function
    <WebMethod()> _
    Public Function GetDealerDiscount(ByVal dbType As String, ByVal ID_Dealer As String, ByVal cardCode As String, _
                                              ByVal itemCode As String) As DataSet
        Try
            Dim ds As New DataSet("ODRF")
            Dim connect As New Connection()
            Dim str As String = String.Empty
            str = "SELECT * FROM OSPP WHERE ItemCode = @Param1 AND CardCode = @Param2"
            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, New Object() {itemCode, cardCode}, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetDOQtyToShip(ByVal dbType As String, ByVal ID_Dealer As String, ByVal itemcode As String, _
                                              ByVal debitNote As String) As DataSet
        Try
            Dim ds As New DataSet("ODLN")
            Dim connect As New Connection()
            Dim str As String = String.Empty
            str = "SELECT T0.DocEntry,QtyToShip FROM ODLN T0 JOIN DLN1 T1 ON T0.DocEntry =  T1.DocEntry WHERE U_DebitNote = @Param1 AND ItemCode = @Param2"
            str += " AND U_Department = 'PAR'"
            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, New Object() {debitNote, itemcode}, dbType)

            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
#End Region
#Region "MRP VSM"
    <WebMethod()> _
    Public Function GetMRPVSMDate(ByVal dbType As String) As DataSet
        Try
            Dim ds As New DataSet("U_AB_MRPVSM_Header")
            Dim connect As New Connection()

            Dim str As String = ""
            str = "SELECT * FROM U_AB_MRPVSM_Header"

            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Utils.WriteLog(ex.Message + "-" + ex.StackTrace)
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetMRPVSMDetail(ByVal dbType As String) As DataSet
        Try
            Dim ds As New DataSet("U_AB_MRPVSM_Detail")
            Dim connect As New Connection()

            Dim str As String = ""
            str = "SELECT * FROM U_AB_MRPVSM_Detail"

            Dim errMsg = connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)

            Return ds
        Catch ex As Exception
            Utils.WriteLog(ex.Message + "-" + ex.StackTrace)
            Throw ex
        End Try
        Return Nothing
    End Function
#End Region
#Region "MRP Part"

    <WebMethod()> _
    Public Function GetMRPItemPartByWareHouse(ByVal dbType As String, ByVal whsCode As String, ByVal department As String) As DataSet
        Dim ds As DataSet
        Dim connect As New Connection()
        connect.setDB(dbType)
        Dim headerTable As String
        Dim detailTable As String
        headerTable = "ORDR"
        detailTable = "RDR1"
        Dim str As String
        Dim condition As String = String.Empty
        str = " SELECT DISTINCT itm.LeadTime, itm.OrdrIntrvl,itm.U_Model, itm.CardCode, itm.U_Class, itm.ItemCode,itm.ItemName, SUM(itm.OnHand) AS OnHand,SUM(so.NumberLine) AS NumberLine FROM ("
        str += " SELECT  T0.LeadTime, T0.OrdrIntrvl, T0.U_Model, T0.CardCode,T0.U_Class,T0.ItemCode,T0.ItemName,SUM(T1.OnHand ) AS OnHand FROM OITM T0 JOIN OITW T1 ON T0.ItemCode =  T1.ItemCode "
        'str += " WHERE T0.ItmsGrpCod = 102 AND T1.WhsCode = @Param1) AS itm LEFT JOIN ("
        str += " WHERE T0.Planingsys = 'M' AND T0.ItmsGrpCod = 102  GROUP BY T0.LeadTime, T0.OrdrIntrvl, T0.U_Model, T0.CardCode,T0.U_Class,T0.ItemCode,T0.ItemName) AS itm LEFT JOIN ("
        str += " SELECT T2.ItemCode, COUNT(T0.DocEntry) AS NumberLine FROM " & headerTable & "  T0 JOIN " & detailTable & "  T1 ON T0.DocEntry  = T1.DocEntry "
        str += " JOIN OITM T2 ON T1.ItemCode = T2.ItemCode"
        str += " WHERE T0.U_Department = @Param1 AND (T2.ItmsGrpCod = 102 And T1.Quantity > 0 And T0.DocDate >= DateAdd(m, -12, GETDATE()))"
        str += " GROUP BY T2.ItemCode) AS so ON itm.ItemCode = so.ItemCode"
        str += " WHERE  OnHand IS NOT NULL  AND NumberLine IS NOT NULL"
        str += " GROUP BY itm.LeadTime, itm.OrdrIntrvl, itm.U_Model, itm.CardCode, itm.U_Class, itm.ItemCode,itm.ItemName"
        str += " ORDER BY NumberLine DESC,OnHand DESC"
        ds = connect.ObjectGetAll_Query(str, New Object() {department}, dbType)
        If ds IsNot Nothing AndAlso ds.Tables.Count > 0 Then
            Dim dsOpenPO As DataSet = GetPartsOpenPO(dbType, department)
            Dim dsOpenSO As DataSet = GetPartsOpenSO(dbType, department)
            Dim dsSea As DataSet = GetPartsOpenPOByType(dbType, department, "0")
            Dim dsAir As DataSet = GetPartsOpenPOByType(dbType, department, "1")
            ds.Tables(0).Columns.Add("OpenPO")
            ds.Tables(0).Columns.Add("OpenSO")
            ds.Tables(0).Columns.Add("SeaQty")
            ds.Tables(0).Columns.Add("AirQty")
            Dim temp As Double = 0
            Dim cumulate As Double = 0
            Dim i As Integer = 1
            For Each r As DataRow In ds.Tables(0).Rows
                r("OpenPO") = 0
                r("OpenSO") = 0
                r("SeaQty") = 0
                r("AirQty") = 0
                Dim existPO() As DataRow = dsOpenPO.Tables(0).Select("ItemCode='" & r("ItemCode").ToString() & "'")
                If existPO.Length > 0 Then
                    r("OpenPO") = Double.Parse(existPO(0)("Quantity").ToString())
                End If
                Dim existSO() As DataRow = dsOpenSO.Tables(0).Select("ItemCode='" & r("ItemCode").ToString() & "'")
                If existSO.Length > 0 Then
                    r("OpenSO") = Double.Parse(existSO(0)("Quantity").ToString())
                End If
                Dim existSea() As DataRow = dsSea.Tables(0).Select("ItemCode='" & r("ItemCode").ToString() & "'")
                If existSea.Length > 0 Then
                    r("SeaQty") = Double.Parse(existSea(0)("Quantity").ToString())
                End If
                Dim existAir() As DataRow = dsAir.Tables(0).Select("ItemCode='" & r("ItemCode").ToString() & "'")
                If existAir.Length > 0 Then
                    r("AirQty") = Double.Parse(existAir(0)("Quantity").ToString())
                End If
            Next
        End If
        Return ds
    End Function
    <WebMethod()> _
    Public Function GetAllItemPartByWareHouse(ByVal dbType As String, ByVal whsCode As String) As DataSet
        Dim ds As DataSet
        Dim connect As New Connection()
        connect.setDB(dbType)
        Dim str As String
        Dim condition As String = String.Empty
        str = "SELECT T1.OnHand, T0.* FROM OITM T0 JOIN OITW T1 ON T0.ItemCode =  T1.ItemCode WHERE T0.ItmsGrpCod = 102 AND T1.WhsCode = @Param1"
        ds = connect.ObjectGetAll_Query(str, New Object() {whsCode}, dbType)
        Return ds
    End Function
    <WebMethod()> _
    Public Function GetPartNumberOfLineItems(ByVal dbType As String, ByVal whsCode As String) As DataSet
        Dim ds As DataSet
        Dim connect As New Connection()
        connect.setDB(dbType)
        Dim str As String
        Dim condition As String = String.Empty
        str = "SELECT T2.ItemCode, COUNT(T0.DocEntry) AS Quantity FROM ORDR T0 JOIN RDR1 T1 ON T0.DocEntry  = T1.DocEntry "
        str += " JOIN OITM T2 ON T1.ItemCode = T2.ItemCode"
        str += " WHERE T2.ItmsGrpCod = 102 AND T1.Quantity >0 AND T0.DocDate >= DATEADD(m,-12,GETDATE())"
        str += " GROUP BY T2.ItemCode"
        str += " Order BY COUNT(T0.DocEntry) DESC"
        ds = connect.ObjectGetAll_Query(str, New Object() {whsCode}, dbType)
        Return ds
    End Function

    <WebMethod()> _
    Public Function GetPartARInvoiceItems(ByVal dbType As String, ByVal whsCode As String) As DataSet
        Dim ds As DataSet
        Dim connect As New Connection()
        connect.setDB(dbType)
        Dim str As String
        Dim condition As String = String.Empty
        str = "SELECT MONTH(T0.DocDate) AS M,YEAR(T0.DocDate) AS Y, SUM( T1.Quantity) AS Quantity,T1.ItemCode  "
        str += " FROM OINV T0 JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry JOIN OITM T2 ON T1.ItemCode = T2.ItemCode"
        str += " WHERE T0.DocDate BETWEEN DATEADD(m,-6,GETDATE()) AND GETDATE() AND ItmsGrpCod = 102"
        str += " GROUP BY T1.ItemCode,MONTH(T0.DocDate),YEAR(T0.DocDate)"
        str += " ORDER BY MONTH(T0.DocDate)"
        ds = connect.ObjectGetAll_Query(str, New Object() {whsCode}, dbType)
        If ds IsNot Nothing AndAlso ds.Tables.Count > 0 Then
            ds.Tables(0).Columns.Add("No")
            Dim month As String = String.Empty
            Dim i As Integer = 8
            For value As Integer = ds.Tables(0).Rows.Count - 1 To 0 Step -1
                If month <> ds.Tables(0).Rows(value)("M").ToString() Then
                    i -= 1
                End If
                month = ds.Tables(0).Rows(value)("M").ToString()
                ds.Tables(0).Rows(value)("No") = i.ToString()
            Next
        End If
        Return ds
    End Function
    <WebMethod()> _
    Public Function GetInternalInventoryTransfer(ByVal dbType As String, ByVal whsCode As String) As DataSet
        Dim ds As DataSet
        Dim connect As New Connection()
        connect.setDB(dbType)
        Dim str As String
        Dim condition As String = String.Empty
        str = "SELECT MONTH(T0.DocDate) AS M,YEAR(T0.DocDate) AS Y, SUM( T1.Quantity) AS Quantity,T1.ItemCode  "
        str += " FROM OWTR T0 JOIN WTR1 T1 ON T0.DocEntry = T1.DocEntry JOIN OITM T2 ON T1.ItemCode = T2.ItemCode"
        If dbType = "VSM" Then
            str += " WHERE T0.DocDate BETWEEN DATEADD(m,-6,GETDATE()) AND GETDATE() AND ItmsGrpCod = 102 AND T0.U_Issue ='XNB'"
        Else
            str += " WHERE T0.DocDate BETWEEN DATEADD(m,-6,GETDATE()) AND GETDATE() AND ItmsGrpCod = 102"
        End If
        str += " GROUP BY T1.ItemCode,MONTH(T0.DocDate),YEAR(T0.DocDate)"
        str += " ORDER BY MONTH(T0.DocDate)"
        ds = connect.ObjectGetAll_Query(str, New Object() {whsCode}, dbType)
        If ds IsNot Nothing AndAlso ds.Tables.Count > 0 Then
            ds.Tables(0).Columns.Add("No")
            Dim month As String = String.Empty
            Dim i As Integer = 8
            For value As Integer = ds.Tables(0).Rows.Count - 1 To 0 Step -1
                If month <> ds.Tables(0).Rows(value)("M").ToString() Then
                    i -= 1
                End If
                month = ds.Tables(0).Rows(value)("M").ToString()
                ds.Tables(0).Rows(value)("No") = i.ToString()
            Next
        End If
        Return ds
    End Function
    <WebMethod()> _
    Public Function GetPartsOpenPOByType(ByVal dbType As String, ByVal department As String, ByVal type As String) As DataSet
        Try
            Dim b As New SAP_Functions
            Dim connect As New Connection()
            Dim ds As New DataSet("OPOR")
            Dim errMsg = connect.setDB(dbType)
            If errMsg.Length = 0 Then
                Dim query = "SELECT SUM(OpenQty) AS Quantity,POR1.ItemCode FROM OPOR JOIN POR1 ON OPOR.DocEntry =  POR1.DocEntry "
                query += " WHERE OPOR.DocDate BETWEEN DATEADD(m,-6,GETDATE()) AND GETDATE() AND DocStatus = 'O' AND U_Department = '" & department & "' AND U_SOType = '" & type & "'"
                query += " GROUP BY POR1.ItemCode "
                ds = connect.ObjectGetAll_Query(query, dbType)
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetPartsOpenPO(ByVal dbType As String, ByVal department As String) As DataSet
        Try
            Dim b As New SAP_Functions
            Dim connect As New Connection()
            Dim ds As New DataSet("OPOR")
            Dim errMsg = connect.setDB(dbType)
            If errMsg.Length = 0 Then
                Dim query = "SELECT SUM(OpenQty) AS Quantity,POR1.ItemCode FROM OPOR JOIN POR1 ON OPOR.DocEntry =  POR1.DocEntry "
                query += " WHERE OPOR.DocDate BETWEEN DATEADD(m,-6,GETDATE()) AND GETDATE() AND DocStatus = 'O' AND U_Department = '" & department & "'"
                query += " GROUP BY POR1.ItemCode "
                ds = connect.ObjectGetAll_Query(query, dbType)
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    <WebMethod()> _
    Public Function GetPartsOpenSO(ByVal dbType As String, ByVal department As String) As DataSet
        Try
            Dim b As New SAP_Functions
            Dim connect As New Connection()
            Dim ds As New DataSet("ORDR")
            Dim errMsg = connect.setDB(dbType)
            If errMsg.Length = 0 Then
                Dim query = "SELECT SUM(OpenQty) AS Quantity,RDR1.ItemCode FROM ORDR JOIN RDR1 ON ORDR.DocEntry = RDR1.DocEntry"
                query += "  WHERE ORDR.DocDate BETWEEN DATEADD(m,-6,GETDATE()) AND GETDATE() AND DocStatus = 'O'  AND U_Department = '" & department & "'"
                query += " GROUP BY RDR1.ItemCode "
                ds = connect.ObjectGetAll_Query(query, dbType)
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
#End Region
End Class